package com.qike.huadongexcel.excelTools;

import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.ComThread;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;

public class JacobExcelTool {

    /**
     * @author Moses *
     * @Date 2019/4/1
     */
        /**
         * Excel对象
         */
        private ActiveXComponent xl = null;
        /**
         * 工作簿对象
         */
        private Dispatch workbooks = null;
        /**
         * 具体工作簿
         */
        private Dispatch workbook = null;
        /**
         * 获得sheets集合对象
         */
        private Dispatch sheets = null;
        /**
         * 当前sheet
         */
        private Dispatch currentSheet = null;

        public ActiveXComponent getXl() {
            return xl;
        }

        public Dispatch getWorkbooks() {
            return workbooks;
        }

        public Dispatch getWorkbook() {
            return workbook;
        }

        /**
         * 打开excel文件
         *
         * @param filepath 文件路径名称
         * @param visible  是否显示打开
         * @param readonly 是否只读方式打开
         */
        public void OpenExcel(String filepath, boolean visible, boolean readonly) {
            try {
                // 清空原始变量
                initComponents();
                //仅允许同时运行一个线程,其他线程锁住,ComThread.InitMTA(true);可同时运行多个
                ComThread.InitSTA();
                // Excel对象
                if (xl == null) {
                    xl = new ActiveXComponent("Excel.Application");
                }
                // 设置是否显示打开excel
                xl.setProperty("Visible", new Variant(visible));
                // 工作簿对象
                if (workbooks == null) {
                    workbooks = xl.getProperty("Workbooks").toDispatch();
                }
                // 打开具体工作簿
                workbook = Dispatch.invoke(
                        workbooks, "Open", Dispatch.Method,
                        new Object[]{filepath, new Variant(false), new Variant(readonly)},
                        new int[1]).toDispatch();
            } catch (Exception e) {
                e.printStackTrace();
                releaseSource();
            }
        }

    /**
     * 读取excel文件
     *
     * @param filepath 文件路径名称
     * @param visible  是否显示打开
     * @param readonly 是否只读方式打开
     */
    public void getExcel(String filepath, boolean visible, boolean readonly) {
        try {
            // 清空原始变量
            initComponents();
            //仅允许同时运行一个线程,其他线程锁住,ComThread.InitMTA(true);可同时运行多个
            ComThread.InitSTA();
            // Excel对象
            if (xl == null) {
                xl = new ActiveXComponent("Excel.Application");
            }
            // 设置是否显示打开excel
//            xl.setProperty("Visible", new Variant(visible));
            // 工作簿对象
            if (workbooks == null) {
                workbooks = xl.getProperty("Workbooks").toDispatch();
            }
            // 打开具体工作簿
            workbook = Dispatch.invoke(
                    workbooks, "Open", Dispatch.Method,
                    new Object[]{filepath, new Variant(false), new Variant(readonly)},
                    new int[1]).toDispatch();
        } catch (Exception e) {
            e.printStackTrace();
            releaseSource();
        }
    }

        /**
         * 工作簿另存为
         *
         * @param filePath 另存为的路径
         */
        public void SaveAs(String filePath) {
            Dispatch.invoke(workbook, "SaveAs", Dispatch.Method, new Object[]{filePath, new Variant(44)}, new int[1]);
        }

        /**
         * 关闭excel文档
         *
         * @param f 含义不明 （关闭是否保存？默认false）
         */
        public void CloseExcel(boolean f, boolean quitXl) {
            try {
                Dispatch.call(workbook, "Save");
                Dispatch.call(workbook, "Close", new Variant(f));
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (quitXl) {
                    releaseSource();
                }
            }
        }

        /**
         * 释放资源
         */
        public void releaseSource() {
            if (xl != null) {
                xl.invoke("Quit", new Variant[]{});
                xl = null;
            }
            workbooks = null;
            ComThread.Release();
            System.gc();
        }

        /**
         * 添加新的工作表(sheet)，（添加后为默认为当前激活的工作表）
         */
        public Dispatch addSheet() {
            return Dispatch.get(Dispatch.get(workbook, "sheets").toDispatch(), "add").toDispatch();
        }

        /**
         * 修改当前工作表的名字
         *
         * @param newName
         */
        public void modifyCurrentSheetName(String newName) {
            Dispatch.put(getCurrentSheet(), "name", newName);
        }

        /**
         * 得到当前工作表的名字
         *
         * @return
         */
        public String getCurrentSheetName() {
            return Dispatch.get(getCurrentSheet(), "name").toString();
        }

        /**
         * 得到工作薄的名字
         *
         * @return
         */
        public String getWorkbookName() {
            if (workbook == null) {
                return null;
            }
            return Dispatch.get(workbook, "name").toString();
        }

        /**
         * 得到sheets的集合对象
         *
         * @return
         */
        public Dispatch getSheets() {
            if (sheets == null) {
                sheets = Dispatch.get(workbook, "sheets").toDispatch();
            }
            return sheets;
        }

        /**
         * 得到当前sheet
         *
         * @return
         */
        public Dispatch getCurrentSheet() {
            currentSheet = Dispatch.get(workbook, "ActiveSheet").toDispatch();
            return currentSheet;
        }

        /**
         * 通过工作表名字得到工作表
         *
         * @param name sheetName
         * @return
         */
        public Dispatch getSheetByName(String name) {
            return Dispatch.invoke(getSheets(), "Item", Dispatch.Get, new Object[]{name}, new int[1]).toDispatch();
        }

        /**
         * 通过工作表索引得到工作表(第一个工作簿index为1)
         *
         * @param index
         * @return sheet对象
         */
        public Dispatch getSheetByIndex(Integer index) {
            return Dispatch.invoke(getSheets(), "Item", Dispatch.Get, new Object[]{index}, new int[1]).toDispatch();
        }

        /**
         * 得到sheet的总数
         *
         * @return
         */
        public int getSheetCount() {
            int count = Dispatch.get(getSheets(), "count").toInt();
            return count;
        }

        /**
         * 调用excel宏
         *
         * @param macroName 宏名
         */
        public void callMacro(String macroName) {
            Dispatch.call(xl, "Run", new Variant(macroName));
//            Dispatch.call(xl, "Run", macroName);
        }

        /**
         * 调用excel宏
         *
         * @param macroName 宏名
         * @param param     传递参数
         */
        public void callMacro(String macroName, Object param) {
            Dispatch.call(xl, "Run", new Variant(macroName), new Variant(param));
        }

        /**
         * 单元格写入值
         *
         * @param sheet    被操作的sheet
         * @param position 单元格位置，如：C1
         * @param type     值的属性 如：value
         * @param value
         */
        public void setValue(Dispatch sheet, String position, String type, Object value) {
            Dispatch cell = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[]{position}, new int[1])
                    .toDispatch();
            Dispatch.put(cell, type, value);
        }

        /**
         * 单元格读取值
         *
         * @param position 单元格位置，如： C1
         * @param sheet
         * @return
         */
        public Variant getValue(String position, Dispatch sheet) {
            Dispatch cell = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[]{position}, new int[1])
                    .toDispatch();
            return Dispatch.get(cell, "Value");
        }

        /**
         * 清空原始变量
         */
        private void initComponents() {
            workbook = null;
            currentSheet = null;
            sheets = null;
        }
    }


